package dbLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import modelLayer.Class;
import modelLayer.ClassRoom;
import modelLayer.Subject;
import modelLayer.Teacher;
import modelLayer.TeachingBlock;

/**
 * This class is a part of the MSAS system. It implements the methods of the interface TeachingBlockIF
 * in order to insert, retrieve, update and delete information about teaching-blocks in a database.
 * 
 @author   Danny M. Pedersen,
 * 		   Anna Kelemen,
 * 		   Jakob Korsgaard
 * @version 1.3
 * 			
 */
public class DBTeachingBlock implements DBTeachingBlockIF {

	private Connection dbConnection;
	
	public DBTeachingBlock(){
		dbConnection = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int insert(Class cl, Subject s, ClassRoom clRoom, Teacher t, String name) throws SQLException {
		
		int rc = -1;
		String query = "INSERT INTO TeachingBlock (class, subject, class_room, teacher, name)"
						+ "values(?, ?, ?, ?, ?)";
		
		try {	
			PreparedStatement ps = dbConnection.prepareStatement(query);
			ps.setInt(1, cl.getId());
			ps.setInt(2, s.getId());
			ps.setInt(3, clRoom.getId());
			ps.setInt(4, t.getId());
			ps.setString(5, name);
			rc = ps.executeUpdate();
			ps.close();		
		}
		catch(SQLException e) {
			System.out.println("Error in insert: " + e.getMessage());	
			throw new SQLException();
		}
		
		return rc;
		
	}

	@Override
	public TeachingBlock find(String name, boolean retrieveAssociation) {
		
		String wClause = "name = '" + name + "'";
		return singleWhere(wClause, retrieveAssociation);
		
	}
	
	@Override
	public TeachingBlock findById(int tbId, boolean retrieveAssociation) {
		
		String wClause = "id = '" + tbId + "'";
		return singleWhere(wClause, retrieveAssociation);
		
	}
	
	@Override
	public List<TeachingBlock> getAllByClass(int classId, boolean retrieveAssociation) {
		
		String wClause = "class = '" + classId + "'";
		return miscWhere(wClause, retrieveAssociation);
	}

	@Override
	public List<TeachingBlock> getAll(boolean retrieveAssociation) {
		
		String wClause = "";
		return miscWhere(wClause, retrieveAssociation);
		
	}
	
	@Override
	public int update(TeachingBlock tb) throws SQLException {
		
		int rc = -1;
		String query = "UPDATE TeachingBlock SET class = ?, subject = ?, class_room = ?, teacher = ?, name = ? WHERE id = ?";
		
		try {
			PreparedStatement ps = dbConnection.prepareStatement(query);
			ps.setInt(1, tb.getCl().getId());
			ps.setInt(2, tb.getSubject().getId());
			ps.setInt(3, tb.getClassRoom().getId());
			ps.setInt(4, tb.getTeacher().getId());
			ps.setString(5, tb.getName());
			ps.setInt(6, tb.getId() );
			rc = ps.executeUpdate();
			ps.close();
		}
		catch (Exception e){
			System.out.println("DEBUG MESSAGE [TEACHINGBLOCK]: update(): " + e);
			throw new SQLException();
		}
		
		return rc;
		
	}

	@Override
	public int remove(int id) {
		
		int rc = -1;
		String query = "DELETE FROM TeachingBlock WHERE id = ?";
		
		try {
			PreparedStatement ps = dbConnection.prepareStatement(query);
			ps.setInt(1, id);
			rc = ps.executeUpdate();
			ps.close();
		}
		catch (Exception e){
			System.out.println("DEBUG MESSAGE [TEACHINGBLOCK]: remove(): "+ e);
		}
		
		return rc;
		
	}
	
	@Override
	public int removeByClassAndSubject(int classId, int subjectId) {
		
		int rc = -1;
		String query = "DELETE FROM TeachingBlock WHERE class = ? and subject = ?";
		
		try {
			PreparedStatement ps = dbConnection.prepareStatement(query);
			ps.setInt(1, classId);
			ps.setInt(2, subjectId);
			rc = ps.executeUpdate();
			ps.close();
		}
		catch (Exception e) {
			System.out.println(e);
		}
		return rc;
	}
	
	/**
	 * Builds a query with a where clause (condition), and returns a single object
	 * to the caller. This method is used when only wanting one object built from 
	 * the database. This method takes the following parameters:
	 *  
	 * @param 	wClause				String			the search condition, and where clause of the SQL query.
	 * @param 	retriveAssociation	boolean			states if we're to retrieve associations in the object aswell.
	 * @return	tb					TeachingBlock	a built TeachingBlock object, based on query results from 
	 * 												the database.
	 */
	private TeachingBlock singleWhere(String wClause, boolean retrieveAssociation ) {
		
		ResultSet rs;
		TeachingBlock tb = null;	
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);	
			if(rs.next()) {
				tb = buildObject(rs);
				
				if(retrieveAssociation) {
					int cId = tb.getCl().getId();
					Class cl = new DBClass().findById(cId, false);
					tb.setCl(cl);
					
					int crId = tb.getClassRoom().getId();
					ClassRoom cr = new DBClassRoom().findById(crId);
					tb.setClassRoom(cr);
					
					int sId = tb.getSubject().getId();
					Subject s = new DBSubject().findById(sId, retrieveAssociation);
					tb.setSubject(s);

					int tId = tb.getTeacher().getId();
					Teacher t = new DBTeacher().findById(tId);
					tb.setTeacher(t);
				}
			}
			stmt.close();
		}
		catch(Exception e) {
			System.out.println("DEBUG MESSAGE [TEACHINGBLOCK]: singleWhere(): " + e);
		}
		
		return tb;
		
	}
	
	/**
	 * Builds a query with a where clause (condition), and returns a collection of objects
	 * to the caller. This method is used when only wanting one or more objects built from 
	 * the database. This method takes the following parameters:
	 *  
	 * @param 	wClause				String				the search condition, and where clause of the SQL query.
	 * @param 	retriveAssociation	boolean				states if we're to retrieve associations in the object aswell.
	 * @return	list				List<TeachingBlock>	a list of built TeachingBlock objects, based on query results 
	 * 													from the database.
	 */
	private ArrayList<TeachingBlock> miscWhere(String wClause, boolean retrieveAssociation) {
		
		ResultSet rs;
		ArrayList<TeachingBlock> list = new ArrayList<>();	
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = dbConnection.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);
			while(rs.next()) {
				TeachingBlock tb = new TeachingBlock();
				tb = buildObject(rs);
				list.add(tb);
			}
			if(retrieveAssociation) {
				for(TeachingBlock tb : list) {
					int clId = tb.getCl().getId();
					Class cl = new DBClass().findById(clId, false);
					tb.setCl(cl);
					
					int crId = tb.getClassRoom().getId();
					ClassRoom cr = new DBClassRoom().findById(crId);
					tb.setClassRoom(cr);
					
					int sId = tb.getSubject().getId();
					Subject s = new DBSubject().findById(sId, retrieveAssociation);
					tb.setSubject(s);
					
					int tId = tb.getTeacher().getId();
					Teacher t = new DBTeacher().findById(tId);
					tb.setTeacher(t);
				}
				stmt.close();
			}
		}
		catch(Exception e) {
			System.out.println("DEBUG MESSAGE [TEACHINGBLOCK]: miscWhere(): " + e);
		}
		
		return list;

	}
	
	/**
	 * Builds a query with a where clause (condition), and returns a string to the caller.
	 * This method takes the following parameters:
	 *  
	 * @param 	wClause				String		the search condition, and where clause of the SQL query.
	 * @return	query				String		an SQL query as a String.
	 */
	private String buildQuery(String wClause) {
		
		String query = "SELECT id, class, subject, class_room, teacher, name FROM TeachingBlock";
		if (wClause.length() > 0) {
			query = query + " WHERE " + wClause;
		}
		
		return query;

	}
	
	/**
	 * builds an object of type TeachingBlock based on the resultset of the SQL query. Returns this
	 * object to the caller.
	 * 
	 * @param 	rs		ResultSet		the resultset from the SQL query.
	 * @return	tb		TeachingBlock	the build TeachingBlock object.
	 */
	private TeachingBlock buildObject(ResultSet rs) {
		
		TeachingBlock tb = new TeachingBlock();
		
		try {
			tb.setId(rs.getInt("id"));
			tb.setName(rs.getString("name"));
			tb.setClassRoom(new ClassRoom(rs.getInt("class_room")));
			tb.setSubject(new Subject(rs.getInt("subject")));
			tb.setCl(new Class(rs.getInt("class")));
			tb.setTeacher(new Teacher(rs.getInt("teacher")));
		}
		catch(Exception e) {
			System.out.println("DEBUG MESSAGE [TEACHINGBLOCK]: buildObject(): " + e);
			e.printStackTrace();
		}
		
		return tb;
		
	}

}
